<?php

for ($i = 0; $i < 100; $i+=5) {
    ${'beforeDeviationSC' . $i} = 0;
}
for ($i = 0; $i < 100; $i+=5) {
    ${'beforeDeviationPR' . $i} = 0;
}
for ($i = 0; $i < 100; $i+=5) {
    ${'afterDeviationSC' . $i} = 0;
}
for ($i = 0; $i < 100; $i+=5) {
    ${'afterDeviationPR' . $i} = 0;
}
/*
  $beforeDeviationSC100 = 0;
  $beforeDeviationSC95 = 0;
  $beforeDeviationSC90 = 0;
  $beforeDeviationSC85 = 0;
  $beforeDeviationSC80 = 0;
  $beforeDeviationSC75 = 0;
  $beforeDeviationSC70 = 0;
  $beforeDeviationSC65 = 0;
  $beforeDeviationSC60 = 0;
  $beforeDeviationSC55 = 0;
  $beforeDeviationSC50 = 0;
  $beforeDeviationSC45 = 0;
  $beforeDeviationSC40 = 0;
  $beforeDeviationSC35 = 0;
  $beforeDeviationSC30 = 0;
  $beforeDeviationSC25 = 0;
  $beforeDeviationSC20 = 0;
  $beforeDeviationSC15 = 0;
  $beforeDeviationSC10 = 0;
  $beforeDeviationSC5 = 0;
  $beforeDeviationSC0 = 0;
 */
/*
  $beforeDeviationPR100 = 0;
  $beforeDeviationPR95 = 0;
  $beforeDeviationPR90 = 0;
  $beforeDeviationPR85 = 0;
  $beforeDeviationPR80 = 0;
  $beforeDeviationPR75 = 0;
  $beforeDeviationPR70 = 0;
  $beforeDeviationPR65 = 0;
  $beforeDeviationPR60 = 0;
  $beforeDeviationPR55 = 0;
  $beforeDeviationPR50 = 0;
  $beforeDeviationPR45 = 0;
  $beforeDeviationPR40 = 0;
  $beforeDeviationPR35 = 0;
  $beforeDeviationPR30 = 0;
  $beforeDeviationPR25 = 0;
  $beforeDeviationPR20 = 0;
  $beforeDeviationPR15 = 0;
  $beforeDeviationPR10 = 0;
  $beforeDeviationPR5 = 0;
  $beforeDeviationPR0 = 0;
 */
/*
  $afterDeviationSC100 = 0;
  $afterDeviationSC95 = 0;
  $afterDeviationSC90 = 0;
  $afterDeviationSC85 = 0;
  $afterDeviationSC80 = 0;
  $afterDeviationSC75 = 0;
  $afterDeviationSC70 = 0;
  $afterDeviationSC65 = 0;
  $afterDeviationSC60 = 0;
  $afterDeviationSC55 = 0;
  $afterDeviationSC50 = 0;
  $afterDeviationSC45 = 0;
  $afterDeviationSC40 = 0;
  $afterDeviationSC35 = 0;
  $afterDeviationSC30 = 0;
  $afterDeviationSC25 = 0;
  $afterDeviationSC20 = 0;
  $afterDeviationSC15 = 0;
  $afterDeviationSC10 = 0;
  $afterDeviationSC5 = 0;
  $afterDeviationSC0 = 0;

  $afterDeviationPR100 = 0;
  $afterDeviationPR95 = 0;
  $afterDeviationPR90 = 0;
  $afterDeviationPR85 = 0;
  $afterDeviationPR80 = 0;
  $afterDeviationPR75 = 0;
  $afterDeviationPR70 = 0;
  $afterDeviationPR65 = 0;
  $afterDeviationPR60 = 0;
  $afterDeviationPR55 = 0;
  $afterDeviationPR50 = 0;
  $afterDeviationPR45 = 0;
  $afterDeviationPR40 = 0;
  $afterDeviationPR35 = 0;
  $afterDeviationPR30 = 0;
  $afterDeviationPR25 = 0;
  $afterDeviationPR20 = 0;
  $afterDeviationPR15 = 0;
  $afterDeviationPR10 = 0;
  $afterDeviationPR5 = 0;
  $afterDeviationPR0 = 0;
 */
$countBeforeDeviationSC = 0;
$countAfterDeviationSC = 0;
$countAfterDeviationPR = 0;
$countBeforeDeviationPR = 0;

$beforeDeviationTotalAverageSC = 0;
$beforeDeviationTotalAveragePR = 0;
$afterDeviationTotalAverageSC = 0;
$afterDeviationTotalAveragePR = 0;

session_start();
include 'conn.php';
if (!empty($_SESSION['centreName'])) {
    include 'conn.php';
    $qry = "Select CentreId from Centre where CentreName='" . $_SESSION['centreName'] . "'";
    $result = mysql_query($qry);
    while ($row = mysql_fetch_array($result)) {
        $centreId = intval($row['CentreId']);
    }
    $cname = $_SESSION['centreName'];
} else {
    $centreId = $_SESSION["centreId"];
    if ($centreId != 0) {
        $qry = "Select CentreName from Centre where CentreId=" . $centreId;
        $result = mysql_query($qry);
        if ($result) {
            $row = mysql_fetch_array($qry);
            $cname = $row['CentreName'];
            $_SESSION["cname"] = $cname;
        }
    }
}
$startDate = $_SESSION["startDate"];
$endDate = $_SESSION["endDate"];
if ($centreId == 0) {
    $clwhere = "client.CentreId is not null";
//echo "testing ";
    $where = "CentreId is not null";
    $kwhere = "INNER JOIN Centre on client.CentreId=centre.CentreId where client.CentreId is not null";
} else {
    $clwhere = "client.CentreId=$centreId";
    $where = "CentreId=$centreId";
    $kwhere = "INNER JOIN Centre on client.CentreId=centre.CentreId where client.CentreId=$centreId";
//echo "testing2";
}


require_once '../Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$startDate1 = date('dmy', strtotime($startDate));
$endDate1 = date('dmy', strtotime($endDate));
//$objConditional1 = new PHPExcel_Style_Conditional();
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
$objPHPExcel->getActiveSheet()->getStyle('A3:Z3')->getFont()->setBold(true);
$objPHPExcel->getProperties()->setTitle("EldercareReport" . $startDate . "to" . $endDate);
if ($centreId == 0) {
    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Eldercare Report from ' . $startDate1 . ' to ' . $endDate1);
} else {
    include 'conn.php';
    $check1 = "Select CentreName from Centre where CentreId=$centreId";
    $resultto = mysql_query($check1);
    while ($row = mysql_fetch_array($resultto)) {
        $cname = $row['CentreName'];
    }
    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Eldercare Report from ' . $startDate1 . ' to ' . $endDate1 . ' for ' . $cname);
}

$objPHPExcel->getActiveSheet()->mergeCells('A1:N1');
$objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


include 'conn.php';
$checkDateSNo = $startDate;
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(9);
$objPHPExcel->getActiveSheet()->mergeCells('C3:D3');
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->SetCellValue('A3', 'S/No');
$objPHPExcel->getActiveSheet()->SetCellValue('B3', 'NRIC');
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
$objPHPExcel->getActiveSheet()->SetCellValue('C3', 'Name');
$column = 'E';
$userType = $_SESSION["userType"];
if ($userType == 'System Admin') {
    $objPHPExcel->getActiveSheet()->SetCellValue($column . '3', 'Centre Name');

    $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(25);
    $column++;
}
$objPHPExcel->getActiveSheet()->SetCellValue($column . '3', 'Citizenship');
$objPHPExcel->getActiveSheet()->getStyle($column . '3')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(13);
$column++;
$objPHPExcel->getActiveSheet()->SetCellValue($column . '3', 'Before Deviation');
$objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(11);
$objPHPExcel->getActiveSheet()->getStyle($column . '3')->getAlignment()->setWrapText(true);
$column++;
$objPHPExcel->getActiveSheet()->SetCellValue($column . '3', 'After Deviation');
$objPHPExcel->getActiveSheet()->getStyle($column . '3')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(11);
$column++;

$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(13);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(13);
$rowCount = 3;
$count = 1;
while ($checkDateSNo <= $endDate) {
    $sqlhols = "Select count(*) from holiday where Date='$checkDateSNo'";
    $resulthols = mysql_query($sqlhols);

    while ($row = mysql_fetch_array($resulthols)) {
        if ($row['count(*)'] == 0) {
            $day = date('l', strtotime($checkDateSNo));
            if ($day != "Sunday" && $day != "Saturday") {
                $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $count);
                $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
                $count++;
                $column++;
            } else {
                $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
            }
        } else {
            $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
        }
    }
}
$objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, 'Total');
$rowCount++;

$ttlattendance = 0;
include 'conn.php';
$sql = "SELECT * from Client $kwhere";
$result = mysql_query($sql);
$sno = 1;
while ($row = mysql_fetch_array($result)) {
    $objPHPExcel->getActiveSheet()->mergeCells('C' . $rowCount . ':D' . $rowCount);
    $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $sno);
    $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $row['NRIC']);
    $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $row['CustomerName']);
    $column = 'E';
    if ($userType == 'System Admin') {
        $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $row['CentreName']);
//$objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getAlignment()->setWrapText(true);
        $column++;
    }
    $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $row['Citizenship']);
    $column++;
    $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $row['BeforeDeviation']);
    $column++;
    $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $row['AfterDeviation']);
    $column++;


    $checkDate = $startDate;
    $workingday = 0;
    $attendance = 0;
    while ($checkDate <= $endDate) {
        $sqlhols1 = "Select count(*) from holiday where Date='$checkDate'";
        $resulthols1 = mysql_query($sqlhols1);

        while ($rowhol = mysql_fetch_array($resulthols1)) {
            if ($rowhol['count(*)'] == 0) {
                $day = date('l', strtotime($checkDate));
                if ($day != "Sunday" && $day != "Saturday") {
                    $sqlattendance = "Select * from attendancerecord where date(SignInTimeStamp)='$checkDate' and NRIC='" . $row['NRIC'] . "' and $where";
                    $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                    $workingday++;
                    $resultattendance = mysql_query($sqlattendance);
                    if (mysql_num_rows($resultattendance) == 1) {
                        $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, '1');
                        $attendance++;
                    } else {
                        $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, '0');
                    }
                    $column++;
                } else {
                    $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                }
            } else {
                $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
            }
        }
    }
    $ttlattendance += $attendance;
    $avg = round(($ttlattendance / $sno), 5);
    $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $attendance);
//$str.="," . $attendance . "\n";
    $styleArray = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
            )
        )
    );
    $style2Array = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THICK, PHPExcel_Style_Border::BORDER_DOUBLE
            )
        )
    );
    $objPHPExcel->getActiveSheet()->getStyle('A3:' . $column . $rowCount)->applyFromArray($styleArray);
    $sno++;
    $rowCount++;

//Before Deviation
    for ($i = 0; $i <= 100; $i+=5) {
        if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == $i) {
            ${'beforeDeviationSC' . $i} += $attendance;
        } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == $i) {
            ${'beforeDeviationPR' . $i} += $attendance;
        }
        if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == $i) {
            ${'afterDeviationSC' . $i} += $attendance;
        } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == $i) {
            ${'afterDeviationPR' . $i} += $attendance;
        }
    }
    /*
      if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 100) {
      $beforeDeviationSC100 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 95) {
      $beforeDeviationSC95 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 90) {
      $beforeDeviationSC90 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 85) {
      $beforeDeviationSC85 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 80) {
      $beforeDeviationSC80 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 75) {
      $beforeDeviationSC75 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 70) {
      $beforeDeviationSC70 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 65) {
      $beforeDeviationSC65 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 60) {
      $beforeDeviationSC60 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 55) {
      $beforeDeviationSC55 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 50) {
      $beforeDeviationSC50 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 45) {
      $beforeDeviationSC45 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 40) {
      $beforeDeviationSC40 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 35) {
      $beforeDeviationSC35 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 30) {
      $beforeDeviationSC30 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 25) {
      $beforeDeviationSC25 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 20) {
      $beforeDeviationSC20 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 15) {
      $beforeDeviationSC15 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 10) {
      $beforeDeviationSC10 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 5) {
      $beforeDeviationSC5 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 0) {
      $beforeDeviationSC0 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 100) {
      $beforeDeviationPR100 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 95) {
      $beforeDeviationPR95 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 90) {
      $beforeDeviationPR90 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 85) {
      $beforeDeviationPR85 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 80) {
      $beforeDeviationPR80 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 75) {
      $beforeDeviationPR75 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 70) {
      $beforeDeviationPR70 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 65) {
      $beforeDeviationPR65 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 60) {
      $beforeDeviationPR60 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 55) {
      $beforeDeviationPR55 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 50) {
      $beforeDeviationPR50 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 45) {
      $beforeDeviationPR45 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 40) {
      $beforeDeviationPR40 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 35) {
      $beforeDeviationPR35 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 30) {
      $beforeDeviationPR30 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 25) {
      $beforeDeviationPR25 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 20) {
      $beforeDeviationPR20 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 15) {
      $beforeDeviationPR15 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 10) {
      $beforeDeviationPR10 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 5) {
      $beforeDeviationPR5 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 0) {
      $beforeDeviationPR0 += $attendance;
      }

      //After Deviation
      if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 100) {
      $afterDeviationPR100 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 95) {
      $afterDeviationPR95 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 90) {
      $afterDeviationPR90 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 85) {
      $afterDeviationPR85 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 80) {
      $afterDeviationPR80 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 75) {
      $afterDeviationPR75 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 70) {
      $afterDeviationPR70 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 65) {
      $afterDeviationPR65 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 60) {
      $afterDeviationPR60 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 55) {
      $afterDeviationPR55 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 50) {
      $afterDeviationPR50 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 45) {
      $afterDeviationPR45 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 40) {
      $afterDeviationPR40 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 35) {
      $afterDeviationPR35 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 30) {
      $afterDeviationPR30 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 25) {
      $afterDeviationPR25 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 20) {
      $afterDeviationPR20 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 15) {
      $afterDeviationPR15 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 10) {
      $afterDeviationPR10 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 5) {
      $afterDeviationPR5 += $attendance;
      } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 0) {
      $afterDeviationPR0 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 100) {
      $afterDeviationSC100 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 95) {
      $afterDeviationSC95 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 90) {
      $afterDeviationSC90 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 85) {
      $afterDeviationSC85 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 80) {
      $afterDeviationSC80 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 75) {
      $afterDeviationSC75 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 70) {
      $afterDeviationSC70 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 65) {
      $afterDeviationSC65 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 60) {
      $afterDeviationSC60 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 55) {
      $afterDeviationSC55 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 50) {
      $afterDeviationSC50 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 45) {
      $afterDeviationSC45 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 40) {
      $afterDeviationSC40 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 35) {
      $afterDeviationSC35 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 30) {
      $afterDeviationSC30 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 25) {
      $afterDeviationSC25 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 20) {
      $afterDeviationSC20 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 15) {
      $afterDeviationSC15 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 10) {
      $afterDeviationSC10 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 5) {
      $afterDeviationSC5 += $attendance;
      } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 0) {
      $afterDeviationSC0 += $attendance;
      } */
}
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, 'Average Attendance:');
$objPHPExcel->getActiveSheet()->mergeCells('A' . $rowCount . ':B' . $rowCount);
$objPHPExcel->getActiveSheet()->getStyle('A' . $rowCount . ':C' . $rowCount)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $rowCount, $avg);
$rowCount++;
$rowCount++;
$rowCount1 = $rowCount;
$rowOrgCount1 = $rowCount;
//Before Deviation first row
$objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':G' . $rowCount)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('B' . $rowCount . ':G' . $rowCount);
$objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':G' . $rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//After Deviation first row
$objPHPExcel->getActiveSheet()->getStyle('I' . $rowCount . ':N' . $rowCount)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('I' . $rowCount . ':N' . $rowCount);
$objPHPExcel->getActiveSheet()->getStyle('I' . $rowCount . ':N' . $rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//setting title header
$objPHPExcel->getActiveSheet()->setCellValue('B' . $rowCount, 'Before Deviation');
$objPHPExcel->getActiveSheet()->setCellValue('I' . $rowCount, 'After Deviation');
$rowCount++;
//setting 2nd rows for BD
$objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, 'Subsidy Level');
$objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, 'SC');
$objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, 'Average');
$objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, 'Subsidy Level');
$objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, 'PR');
$objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, 'Average');
//setting 2nd rows for AD
$objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, 'Subsidy Level');
$objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, 'SC');
$objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowCount, 'Average');
$objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowCount, 'Subsidy Level');
$objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowCount, 'PR');
$objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowCount, 'Average');
$rowCount++;
$rowOrgCount = $rowCount;

//BD table info
//BEFORE DEVIATION DETAILS
for ($i = 100; $i >= 0; $i-=5) {
    $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $i . '%');
    $sqlbdsc = "SELECT count(*) from client where BeforeDeviation=$i and Citizenship='SC' and $where";
    $sqlbdpr = "SELECT count(*) from client where BeforeDeviation=$i and Citizenship='PR' and $where";
    $resultbdsc = mysql_query($sqlbdsc);
    $resultbdpr = mysql_query($sqlbdpr);

    while ($rowbdsc = mysql_fetch_array($resultbdsc)) {
        if ($rowbdsc['count(*)'] == 0) {
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, '0');
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, '0');
        } else {
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $rowbdsc['count(*)']);

            $countBeforeDeviationSC +=$rowbdsc['count(*)'];
            for ($j = 0; $j <= 100; $j+=5) {
                if ($j == $i) {
                    $average = round(${'beforeDeviationSC' . $j} / $workingday, 5);
                    $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                    $beforeDeviationTotalAverageSC +=$average;
                }
            }
            /*
              if ($i == 100) {
              $average = $beforeDeviationSC100 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);

              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 95) {
              $average = $beforeDeviationSC95 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 90) {
              $average = $beforeDeviationSC90 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);

              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 85) {
              $average = $beforeDeviationSC85 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);

              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 80) {
              $average = $beforeDeviationSC80 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 75) {
              $average = $beforeDeviationSC75 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 70) {
              $average = $beforeDeviationSC70 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);

              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 65) {
              $average = $beforeDeviationSC65 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 60) {
              $average = $beforeDeviationSC60 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);

              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 55) {
              $average = $beforeDeviationSC55 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 50) {
              $average = $beforeDeviationSC50 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);

              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 45) {
              $average = $beforeDeviationSC45 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 40) {
              $average = $beforeDeviationSC40 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);

              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 35) {
              $average = $beforeDeviationSC35 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 30) {
              $average = $beforeDeviationSC30 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);

              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 25) {
              $average = $beforeDeviationSC25 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 20) {
              $average = $beforeDeviationSC20 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);

              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 15) {
              $average = $beforeDeviationSC15 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 10) {
              $average = $beforeDeviationSC10 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);

              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 5) {
              $average = $beforeDeviationSC5 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } else if ($i == 0) {
              $average = $beforeDeviationSC0 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
              $beforeDeviationTotalAverageSC +=$average;
              } */

            /* $sqlttlbdsc = "SELECT COUNT( * ) AS countttlbdsc
              FROM attendancerecord, client
              WHERE DATE( SignInTimeStamp )
              BETWEEN  '$startDate'
              AND  '$endDate'
              AND BeforeDeviation =$i AND Citizenship='SC' AND $clwhere
              AND client.NRIC = attendancerecord.NRIC";
              $resulttlbdsc = mysql_query($sqlttlbdsc);
              while ($rowttlbdsc = mysql_fetch_array($resulttlbdsc)) {
              $roundttlbdsc = round($rowttlbdsc['countttlbdsc'] - $workingday / $rowbdsc['count(*)'], 3);
              $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $roundttlbdsc);
              } */
        }
    }

    $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $i . '%');

    while ($rowbdpr = mysql_fetch_array($resultbdpr)) {
        if ($rowbdpr['count(*)'] == 0) {
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, '0');
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, '0');
        } else {
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $rowbdpr['count(*)']);

            $countBeforeDeviationPR +=$rowbdpr['count(*)'];
            for ($j = 0; $j <= 100; $j+=5) {
                if ($j == $i) {
                    $average = round(${'beforeDeviationPR' . $j} / $workingday, 5);
                    $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                    $beforeDeviationTotalAveragePR +=$average;
                }
            }
            /*
              if ($i == 100) {
              $average = $beforeDeviationPR100 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);

              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 95) {
              $average = $beforeDeviationPR95 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 90) {
              $average = $beforeDeviationPR90 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);

              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 85) {
              $average = $beforeDeviationPR85 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 80) {
              $average = $beforeDeviationPR80 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);

              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 75) {
              $average = $beforeDeviationPR75 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 70) {
              $average = $beforeDeviationPR70 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);

              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 65) {
              $average = $beforeDeviationPR65 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 60) {
              $average = $beforeDeviationPR60 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);

              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 55) {
              $average = $beforeDeviationPR55 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 50) {
              $average = $beforeDeviationPR50 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);

              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 45) {
              $average = $beforeDeviationPR45 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 40) {
              $average = $beforeDeviationPR40 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);

              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 35) {
              $average = $beforeDeviationPR35 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 30) {
              $average = $beforeDeviationPR30 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);

              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 25) {
              $average = $beforeDeviationPR25 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);

              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 20) {
              $average = $beforeDeviationPR20 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 15) {
              $average = $beforeDeviationPR15 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 10) {
              $average = $beforeDeviationPR10 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);

              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 5) {
              $average = $beforeDeviationPR5 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } else if ($i == 0) {
              $average = $beforeDeviationPR0 / $workingday;

              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
              $beforeDeviationTotalAveragePR +=$average;
              } */
            /* $sqlttlbdpr = "SELECT COUNT( * ) AS countttlbdpr
              FROM attendancerecord, client
              WHERE DATE( SignInTimeStamp )
              BETWEEN  '$startDate'
              AND  '$endDate'
              AND BeforeDeviation =$i AND Citizenship='PR' AND $clwhere
              AND client.NRIC = attendancerecord.NRIC";
              $resulttlbdpr = mysql_query($sqlttlbdpr);
              while ($rowttlbdpr = mysql_fetch_array($resulttlbdpr)) {
              $roundttlbdpr = round($rowttlbdsc['countttlbdpr'] - $workingday / $rowbdpr['count(*)'], 3);
              $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $roundttlbdpr);
              } */
        }
    }
    $rowCount++;
}
$objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':G' . $rowCount)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, 'Total');
$objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $countBeforeDeviationSC);
$objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $beforeDeviationTotalAverageSC);
$objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, 'Total');
$objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $countBeforeDeviationPR);
$objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $beforeDeviationTotalAveragePR);

//AFTER DEVIATION TABLE INFO
for ($j = 100; $j >= 0; $j-=5) {
    $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowOrgCount, $j . '%');
    $sqladsc = "SELECT count(*) from client where AfterDeviation=$j and Citizenship='SC' and $where";
    $sqladpr = "SELECT count(*) from client where AfterDeviation=$j and Citizenship='PR' and $where";
    $resultadsc = mysql_query($sqladsc);
    $resultadpr = mysql_query($sqladpr);

    while ($rowadsc = mysql_fetch_array($resultadsc)) {
        if ($rowadsc['count(*)'] == 0) {
            $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowOrgCount, '0');
            $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, '0');
        } else {

            $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowOrgCount, $rowadsc['count(*)']);

            $countAfterDeviationSC +=$rowadsc['count(*)'];
            for ($k = 0; $k <= 100; $k+=5) {
                if ($k == $j) {
                    $average = round(${'afterDeviationSC' . $k} / $workingday, 5);
                    $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                    $afterDeviationTotalAverageSC +=$average;
                }
            }
        }
    }

    $objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowOrgCount, $j . '%');

    while ($rowadpr = mysql_fetch_array($resultadpr)) {
        if ($rowadpr['count(*)'] == 0) {
            $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowOrgCount, '0');
            $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, '0');
        } else {
            $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowOrgCount, $rowadpr['count(*)']);
            $countAfterDeviationPR +=$rowadpr['count(*)'];
            for ($k = 0; $k <= 100; $k+=5) {
                if ($k == $j) {
                    $average = round(${'afterDeviationPR' . $k} / $workingday, 5);
                    $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                    $afterDeviationTotalAveragePR +=$average;
                }
            }
            /*
              if ($j == 100) {
              $average = $afterDeviationPR100 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 95) {
              $average = $afterDeviationPR95 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 90) {
              $average = $afterDeviationPR90 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 85) {
              $average = $afterDeviationPR85 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 80) {
              $average = $afterDeviationPR80 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 75) {
              $average = $afterDeviationPR75 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 70) {
              $average = $afterDeviationPR70 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 65) {
              $average = $afterDeviationPR65 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 60) {
              $average = $afterDeviationPR60 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 55) {
              $average = $afterDeviationPR55 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 50) {
              $average = $afterDeviationPR50 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 45) {
              $average = $afterDeviationPR45 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 40) {
              $average = $afterDeviationPR40 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 35) {
              $average = $afterDeviationPR35 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 30) {
              $average = $afterDeviationPR30 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 25) {
              $average = $afterDeviationPR25 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 20) {
              $average = $afterDeviationPR20 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 15) {
              $average = $afterDeviationPR15 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 10) {
              $average = $afterDeviationPR10 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 5) {
              $average = $afterDeviationPR5 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } else if ($j == 0) {
              $average = $afterDeviationPR0 / $workingday;
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
              $afterDeviationTotalAveragePR += $average;
              } */

            /* $sqlttladpr = "SELECT COUNT( * ) AS countttladpr
              FROM attendancerecord, client
              WHERE DATE( SignInTimeStamp )
              BETWEEN  '$startDate'
              AND  '$endDate'
              AND AfterDeviation =$j AND Citizenship='PR' AND $clwhere
              AND client.NRIC = attendancerecord.NRIC";
              $resulttladpr = mysql_query($sqlttladpr);
              while ($rowttladpr = mysql_fetch_array($resulttladpr)) {
              $roundttladpr = round($rowttladsc['countttladpr'] - $workingday / $rowadpr['count(*)'], 3);
              $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $roundttladpr);
              } */
        }
    }
    $rowOrgCount++;
}
$objPHPExcel->getActiveSheet()->getStyle('I' . $rowCount . ':N' . $rowCount)->getFont()->setBold(true);


$objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, 'Total');
$objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, $countAfterDeviationSC);
$objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowCount, $afterDeviationTotalAverageSC);
$objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowCount, 'Total');
$objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowCount, $countAfterDeviationPR);
$objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowCount, $afterDeviationTotalAveragePR);

$rowCount = $rowCount - 1;
$rowOrgCount = $rowOrgCount - 1;
$objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount1 . ':' . 'G' . $rowCount)->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('I' . $rowOrgCount1 . ':' . 'N' . $rowOrgCount)->applyFromArray($styleArray);
$rowCount++;
$rowOrgCount++;
$objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':' . 'G' . $rowCount)->applyFromArray($style2Array);
$objPHPExcel->getActiveSheet()->getStyle('I' . $rowOrgCount . ':' . 'N' . $rowOrgCount)->applyFromArray($style2Array);

//creating the excel file
//MAILING
//header('Content-Disposition: attachment;filename="EldercareReport' . $startDate1 . 'to' . $endDate1 . '.xlsx"');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-Type: application/vnd.ms-excel');
if ($centreId == 0) {
    header('Content-Disposition: attachment;filename="EldercareReport' . $startDate1 . 'to' . $endDate1 . '.xlsx"');
} else {
    include 'conn.php';
    $check1 = "Select CentreName from Centre where CentreId=$centreId";
    $resultto = mysql_query($check1);
    while ($row = mysql_fetch_array($resultto)) {
        $cname = $row['CentreName'];
    }
    header('Content-Disposition: attachment;filename="EldercareReport' . $startDate1 . 'to' . $endDate1 . '-' . $cname . '.xlsx"');
}


header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit();
//$objWriter->save(str_replace('export_1.php', 'EldercareReport' . $startDate1 . 'to' . $endDate1 . '.xlsx', __FILE__));
//$objWriter->save('php://output');
?>

